In this tutorial, you will learn how to quickly insert current time in Excel as a static timestamp and dynamic formula. Also, we'll look at how to convert timestamp to date and get hours, minutes or seconds from a timestamp.

There are a number of ways to insert time into Excel worksheets. Which one to use depends on whether you want a static timestamp or a dynamic value that updates automatically to reflect the current date and time.

To insert a timestamp into an Excel cell, use one of the following keyboard shortcuts:

The result is a static value that won't change when a workbook is recalculated.

Tip. The timestamp is inserted in the default date/time format. To have the date and time displayed differently, change the Number format applied to the cell. For more information, please see Excel date format and Excel time format.

If you aim to insert the current date and time as a dynamic value that refreshes automatically, then use the Excel NOW function.

The formula is as simple as it can possibly be, no arguments are required:

=NOW()

Like the timestamp shortcuts, the NOW function inserts the current date and time in the default format defined in your Region settings. You can easily change this by applying a custom date and time format.

Here are a few examples of how the Excel NOW function results can be formatted:

When using the NOW function in Excel, there are a few things to keep in mind:

If you wish to insert only the current time without a date, you have the following choices:

Use a regular NOW formula, and then apply the time format you want to the result:

=NOW()

Please remember, this will only change the display representation. The actual value stored in a cell will still be a decimal number consisting of an integer representing the date and a fractional part representing the time.

Make use of the INT function to round the decimal number returned by NOW() down to the nearest integer. And then, subtract the integer part representing today's date so that only the fractional part representing the current time is output.

=NOW() - INT(NOW())

By default, the formula returns a decimal number representing the time internally in Excel. To get a correct result, apply the Time format to the cell.

The following screenshot demonstrates both formulas in action. Pay attention that although the formatted time values look the same (column D), the actual values stored in cells (column B) are different - the results returned by the second formula (B16:B20) contain only the fractional part.

To insert the current hour as a number between 0 (12:00 AM) and 23 (11:00 PM), use the HOUR and NOW functions together:

=HOUR(NOW())

Yep, it's that simple :)

Assume you have a column of timestamps in your worksheet from which you wish to remove the time value and keep only the date. This can be done in a few different ways.

The easiest way to convert a timestamp to a date is to format it in such a way that only a date is visible in a cell. Here's how:

If you'd rather have a date in a separate cell, enter a formula like =A3 (where A3 is the original timestamp) in any empty cell in the same row, and set the Date format for that cell.

For example, here's how you can change a timestamp to the default long date format:

In Excel's internal system, a date time value is stored as a decimal number where time is represented by a fractional part. An easy way to remove that part is using the INT function that rounds a decimal down to the integer:

=INT(A3)

Or you can extract the date with the help of the DATE function:

=DATE(YEAR(A3), MONTH(A3), DAY(A3))

Whichever formula you choose, set the Date format to the formula cells and you'll get the result you are looking for:

Note. All the methods described above assume a timestamp is a date time value and not a text string. If your timestamp is a text value, then you need to convert text to date first. Or you can split a text string into different columns, or extract a substring representing the date.

To extract a specific time unit from a time stamp, you can use the following functions:

HOUR(serial_number) - returns an hour of a time value, as an integer from 0 (12:00 am) to 23 (11:00 pm).

MINUTE(serial_number) - gets the minutes of a time value, as an integer from 0 to 59.

SECOND(serial_number) - returns the seconds of a time value, as an integer from 0 to 59.

In all three functions, serial_number can be supplied as a reference to the cell containing the time, text string enclosed in double quotes (for example, "6:00 AM"), decimal number (e.g. 0.25 representing 6:00 AM), or the result of another function.

In the image below, the following formulas are used.

To return hours from the timestamp in A2:

=HOUR(A2)

To get minutes from the timestamp in A2:

=MINUTE(A2)

To extract seconds from the timestamp in A2:

=SECOND(A2)

When you type a time value in a cell, in most cases Microsoft Excel recognizes it as such and automatically applies the Time format. If Excel does not understand what you are entering, the TIME function can help. The syntax is intuitive and straightforward:

The hour, minute and second arguments can be provided as numbers from 0 to 32767. The supplied numbers are processed in this way:

For example:

The Excel TIME function is particularly useful when it comes to combining individual units into a single time value.

Assuming you have hours in A3, minutes in B3 and seconds in C3, the following formula will put them together:

=TIME(A3, B3, C3)

That's how to insert the current date and time in Excel with shortcuts and formulas. I thank you for reading and hope to see you on our blog next week!

Insert time in Excel - examples (.xlsx file)